<?php
class omeanalysts_mdl_ome_deliveryDetailold extends dbeav_model{

    //var $has_export_cnf = true;

    var $export_name = '发货明细表（归档）';

    public function table_name($real=false){
        $table_name = 'ome_delivery';
        if($real){
            return kernel::database()->prefix.$table_name;
        }else{
            return $table_name;
        }
    }

    public function count($filter=null){

        $where = $this->_filter($filter); 

        $sql = "SELECT count(*) as _count from sdb_wms_deliverycomp a 
                     inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id
                     inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id
                     inner join sdb_wms_deliverynote  dn on dn.id=d.deliverynote_id
                     inner join sdb_archive_wmsdelivery e on d.delivery_bn = e.delivery_bn
                     inner join sdb_archive_wmsdelivery_bill db on e.delivery_id = db.delivery_id 
                     inner join sdb_archive_wmsdelivery_items g on e.delivery_id = g.delivery_id 
                     inner join sdb_ome_shop f on e.shop_id = f.shop_id
                     inner JOIN sdb_archive_delivery od on ( e.outer_delivery_bn = od.delivery_bn )
                     inner JOIN sdb_archive_delivery_order odo on ( od.delivery_id = odo.delivery_id )
                     inner JOIN sdb_archive_orders os on (odo.order_id = os.order_id)
                     inner JOIN sdb_ome_branch sob on (sob.branch_id = e.branch_id)
                     where ".$where." ";

        $row = $this->db->select($sql);
        return intval($row[0]['_count']);
    }

    public function getlist($cols='*', $filter=array(), $offset=0, $limit=-1, $orderType=null){
  

        if(!$filter['time_from'] && !$filter['time_to']){
            return false;
        }else{
          $orderItem = app::get('archive')->model('order_items');
          $useraMdl = app::get('desktop')->model('users');
          $is_price = $useraMdl ->getlist('is_price',array('user_id'=>$filter['op_id']));

            $where = $this->_filter($filter); 
            //var_dump($where);
            $sql = "SELECT f.`name`,f.AXCustomer,d.ax_shopname customername,d.axorderno as ax_bn,os.order_bn,os.order_id,e.delivery_bn,FROM_UNIXTIME(a.deliverycomp_time, '%Y-%m-%d %H:%i:%S') as delivery_time,g.bn
                        ,g.product_name as material_name,smbme.unit unit,dn.deliverynote_bn,FROM_UNIXTIME(db.delivery_time, '%Y-%m-%d %H:%i:%S') as deliveryed_time,g.number,e.logi_name,db.logi_no,e.ship_province,d.carno,e.delivery_cost_actual flatFeePrice,sob.`name` as branch,f.shop_type 
                         from sdb_wms_deliverycomp a 
                         inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id
                         inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id
                         inner join sdb_wms_deliverynote  dn on dn.id=d.deliverynote_id
                         inner join sdb_archive_wmsdelivery e on d.delivery_bn = e.delivery_bn
                         inner join sdb_archive_wmsdelivery_bill db on e.delivery_id = db.delivery_id 
                         inner join sdb_archive_wmsdelivery_items g on e.delivery_id = g.delivery_id 
                         inner join sdb_material_basic_material smbm on smbm.material_bn=g.bn
                         INNER JOIN sdb_material_basic_material_ext smbme on smbm.bm_id=smbme.bm_id
                         inner join sdb_ome_shop f on e.shop_id = f.shop_id
                         inner JOIN sdb_archive_delivery od on ( e.outer_delivery_bn = od.delivery_bn )
                         inner JOIN sdb_archive_delivery_order odo on ( od.delivery_id = odo.delivery_id )
                         inner JOIN sdb_archive_orders os on (odo.order_id = os.order_id)
                         inner JOIN sdb_ome_branch sob on (sob.branch_id = e.branch_id)
                         where ".$where." ";
            $rows = $this->db->selectLimit($sql,$limit,$offset);
            foreach ($rows as $key => $value) 
            {
                $divide_order_fee = $orderItem ->getlist('divide_order_fee,nums',array('bn'=>$value['bn'],'order_id'=>$value['order_id']));
                
                $rows[$key]['delivery_time'] = strtotime($value['delivery_time']);
                 $rows[$key]['deliveryed_time'] = strtotime($value['deliveryed_time']);
                if($is_price[0]['is_price'] == 'true') {
                    $rows[$key]['divide_order_fee'] = round($divide_order_fee[0]['divide_order_fee']/$divide_order_fee[0]['nums'],4);
                }else{
                    $rows[$key]['divide_order_fee'] = '-';
                    $rows[$key]['flatFeePrice'] = '-';
                }
                $salesPlatfrom = '其他';
               if ($value['shop_type'] != '') {
                   $salesPlatfrom = ome_shop_type::shop_name($value['shop_type']);
               }
                $rows[$key]['shop_type'] = $salesPlatfrom;
            }

            return $rows;
        }
        
    }
    
    public function searchOptions(){
        $parentOptions = parent::searchOptions();
        $childOptions = array(
            'order_bn'=>app::get('base')->_('订单号'),
        );
        return $Options = array_merge($parentOptions,$childOptions);
    }

    public function _filter($filter,$tableAlias=null,$baseWhere=null){


        $where = array(1);
       if(empty($filter['time_from']))
        {
          $where[]=' 1=2 ';
        }
        if(isset($filter['time_from']) && $filter['time_from']){
            $where[] = ' a.deliverycomp_time >='.strtotime($filter['time_from']);
        }
        if(isset($filter['time_to']) && $filter['time_to']){
            $where[] = ' a.deliverycomp_time <'.(strtotime($filter['time_to'])+86400);
        }
        if(isset($filter['shop_id'])   && $filter['shop_id']){
            $where[] = ' f.shop_id  =\''.addslashes($filter['shop_id']).'\'';
        }
        if(!$filter['shop_id'] && $filter['shop_ids']){
            $where[] = ' f.shop_id IN (\''.implode('\',\'', $filter['shop_ids']).'\')';
        }
        unset($filter['shop_id']);
        if(isset($filter['AXCustomer']) && $filter['AXCustomer']){
            $where[]= ' d.ax_shopname  =\''.addslashes($filter['AXCustomer']).'\'';

        }  
        if(isset($filter['customercode']) && $filter['customercode']){
            $where[]= ' d.ax_name  =\''.addslashes($filter['customercode']).'\'';

        }

        if(isset($filter['branch'])   && $filter['branch']){
            $where[] = ' sob.branch_id  =\''.addslashes($filter['branch']).'\'';
        }
        if(!$filter['branch'])
        {

            $Obranch = app::get('ome')->model('branch');
            $branch_ids = $Obranch->getList('branch_id');

            foreach ($branch_ids as $key => $value) {
              $branchIds[]=$value['branch_id'];
            }
            $where[] = ' sob.branch_id IN (\''.implode('\',\'', $branchIds).'\')';
        }
        unset($filter['branch']);

        if(isset($filter['deliverycomp_bn'])  && $filter['deliverycomp_bn']){
            $where[] = ' a.deliverycomp_bn =\''.addslashes($filter['deliverycomp_bn']).'\'';
        }
        if(isset($filter['p_bn']) && $filter['p_bn']){
            $where[] = ' g.bn =\''.addslashes($filter['p_bn']).'\'';
        }
           /*
             * 获取操作员管辖仓库
             */
       
           //  $oBranch = app::get('ome')->model('branch');
           //  $is_super = kernel::single('desktop_user')->is_super();
           //  if (!$is_super){
           //      $branch_ids = $oBranch->getBranchByUser(true);
           //      if ($branch_ids){
           //      $branch =implode(',',$branch_ids);
           //     //var_dump($branch_ids,$branch);
           //      $where[] = ' sob.branch_id in ('.$branch.')';
                            

           //      }
           // }
          
        return implode($where,' AND ');
    }

    public function get_schema(){
        $schema = array (
            'columns' => array (
                'name' =>
                array (
                  'type' => 'varchar(255)',
                  'required' => true,
                  'label' => '店铺',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                'AXCustomer' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '客户编码',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                'customername' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '客户',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '100',
                ),
                'ax_bn' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => true,
                  'label' => 'AX订单号',
                  'comment' => 'AX订单号',
                  'editable' => false,
                  'width' =>140,
                  'searchtype' => 'has',
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'order_bn' =>
                array(
                    'type' => 'varchar(32)',
                    'required' => true,
                    'label' => '订单号',
                    'is_title' => true,
                    'width' => 125,
                    'searchtype' => 'nequal',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'in_list' => true,
                    'default_in_list' => false,
                ),
                'delivery_bn' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => true,
                  'label' => '发货单号',
                  'comment' => '配送流水号',
                  'editable' => false,
                  'width' =>140,
                  'searchtype' => 'has',
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'delivery_time' =>
                array (
                  'type' => 'time',
                  'label' => '发货完成时间',
                  'comment' => '发货时间',
                  'editable' => false,
                  'in_list' => true,
                ),
                'bn' =>
                array (
                  'type' => 'varchar(30)',
                  'label' => '料品',
                  'editable' => false,
                  'is_title' => true,
                ),
                'material_name' =>
                array (
                  'type' => 'varchar(200)',
                  'required' => true,
                  'label' => '品名',
                  'is_title' => true,
                  'default_in_list' => true,
                  'width' => 260,
                  'searchtype' => 'has',
                  'editable' => false,
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                ),
                'unit' =>
                array (
                  'type' => 'varchar(200)',
                  'required' => true,
                  'label' => '单位',
                  'is_title' => true,
                  'default_in_list' => true,
                  'width' => 70,
                  'searchtype' => 'has',
                  'editable' => false,
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                ),
                 'deliverynote_bn' =>
                array(
                    'type' => 'varchar(32)',
                    'required' => true,
                    'label' => '送货单号',
                    'is_title' => true,
                    'width' => 135,
                    'searchtype' => 'has',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'in_list' => true,
                    'default_in_list' => true,
                ),
                 'deliveryed_time' =>
                array (
                  'type' => 'time',
                  'label' => '已发货时间',
                  'comment' => '已发货时间',
                  'editable' => false,
                  'in_list' => true,
                ),
                'number' =>
                array (
                  'type' => 'number',
                  'label' => '数量',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                ),
                'logi_name'=>
                array(
                    'type'=>'varchar(32)',
                    'label'=>'快递公司',
                     'in_list' => true,
                  'default_in_list' => true,
                ),
                'logi_no' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => true,
                  'label' => '快递单号',
                  'editable' => false,
                  'width' =>140,
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'ship_province'=>
                array(
                    'type'=>'varchar(32)',
                    'label'=>'省份',
                     'in_list' => true,
                  'default_in_list' => true,
                ),
                'carno' =>
                array (
                  'type' => 'text',
                  'label' => '车牌号',
                  'comment' => '车牌号',
                  'editable' => false,
                  'width' =>140,
                  'searchtype' => 'has',
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                ),
                'flatFeePrice' =>
                array (
                  'type' => 'number',
                  'label' => '快递费用',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                ),
                 'branch' =>
                array(
                    'type' => 'varchar(50)',
                    'required' => true,
                    'label' => '仓库',
                    'is_title' => true,
                    'width' => 135,
                    'searchtype' => 'has',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'in_list' => true,
                    'default_in_list' => true,
                ),
                'divide_order_fee' =>
                array (
                  'type' => 'number',
                  'label' => '实付金额',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                ),
                'shop_type' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => false,
                  'label' => '店铺类型',
                  'in_list' => true,
                  'default_in_list' => true,
                  'width' => '70'
                ),
            ),
            'idColumn' => 'name',
            'in_list' => array (
                0 => 'name',
                1 => 'AXCustomer',
                2 => 'customername',
                3 => 'ax_bn',
                4 => 'order_bn',
                6 => 'delivery_bn',
                7 => 'delivery_time',
                8 => 'bn',
                9 => 'material_name',
                10 => 'unit',
                11 => 'deliverynote_bn',
                12 => 'deliveryed_time',
                13 => 'number',
                14 => 'logi_name',
                15 => 'logi_no',
                16 => 'ship_province',
                17 => 'carno',
                18 => 'flatFeePrice',
                19 => 'branch',
                20 => 'divide_order_fee',
                21 => 'shop_type',    
            ),
            'default_in_list' => array (
                0 => 'name',
                1 => 'AXCustomer',
                2 => 'customername',
                3 => 'ax_bn',
                4 => 'order_bn',
                6 => 'delivery_bn',
                7 => 'delivery_time',
                8 => 'bn',
                9 => 'material_name',
                10 => 'unit',
                11 => 'deliverynote_bn',
                12 => 'deliveryed_time',
                13 => 'number',
                14 => 'logi_name',
                15 => 'logi_no',
                16 => 'ship_province',
                17 => 'carno',
                18 => 'flatFeePrice',
                19 => 'branch',
                20 => 'divide_order_fee',
                21 => 'shop_type',    
            ),
        );
        return $schema;
    }

    /**
     * 获得日志类型(non-PHPdoc)
     * @see dbeav_model::getLogType()
     */
    public function getLogType($logParams) {
        $type = $logParams['type'];
        $logType = 'none';
        if ($type == 'export') {
            $logType = $this->exportLogType($logParams);
        }
        elseif ($type == 'import') {
            $logType = $this->importLogType($logParams);
        }
        return $logType;
    }
    /**
     * 导出日志类型
     * @param Array $logParams 日志参数
     */
    public function exportLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_export';
        return $type;
    }
    /**
     * 导入操作日志类型
     * @param Array $logParams 日志参数
     */
    public function importLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_import';
        return $type;
    }

    function io_title( $filter, $ioType='csv' ){

        switch( $filter ){
            case 'delivery':
                $this->oSchema['csv'][$filter] = array(
                    '*:店铺' => 'name',
                    '*:客户编码' => 'AXCustomer',
                    '*:客户' => 'customername',
                    '*:AX订单号' => 'ax_bn',
                    '*:订单号' => 'order_bn',
                    '*:发货单号' => 'delivery_bn',
                    '*:发货完成时间' => 'delivery_time',
                    '*:料号' => 'bn',
                    '*:品名' => 'material_name',
                    '*:单位' => 'unit',
                    '*:送货单号' => 'deliverynote_bn',
                    '*:已发货时间' => 'deliveryed_time',
                    '*:数量' => 'number',
                    '*:快递公司' => 'logi_name',
                    '*:快递单号' => 'logi_no',
                    '*:省份' => 'ship_province',
                    '*:车牌' => 'carno',
                    '*:快递费用' => 'flatFeePrice',
                    '*:仓库' => 'branch',
                    '*:实付金额' => 'divide_order_fee',
                    '*:店铺类型' => 'shop_type',
                );
                break;
        }
        $this->ioTitle[$ioType][$filter] = array_keys( $this->oSchema[$ioType][$filter] );
        return $this->ioTitle[$ioType][$filter];
    }

    //根据查询条件获取导出数据
    public function getExportDataByCustom($fields, $filter, $has_detail, $curr_sheet, $start, $end){
          //根据选择的字段定义导出的第一行标题
          if($curr_sheet == 1){

              $title = array();
              foreach($this->io_title('delivery') as $k => $v ){
                  $title[] = mb_convert_encoding($v, 'GBK', 'UTF-8');
              }

              $data['content']['main'][0] = '"'.implode('","',$title).'"';
          }

          $orderItem = app::get('ome')->model('order_items');
          $useraMdl = app::get('desktop')->model('users');
          $is_price = $useraMdl ->getlist('is_price',array('user_id'=>$filter['op_id']));

          $where = $this->_filter($filter);
          $sql = "SELECT f.`name`,f.AXCustomer,f.customername,d.axorderno,os.order_bn,os.order_id,e.delivery_bn,FROM_UNIXTIME(a.deliverycomp_time, '%Y-%m-%d %H:%i:%S') as delivery_time,g.bn
                          ,g.product_name as material_name,smbme.unit unit,dn.deliverynote_bn,FROM_UNIXTIME(db.delivery_time, '%Y-%m-%d %H:%i:%S') as deliveryed_time,g.number,e.logi_name,db.logi_no,e.ship_province,d.carno,e.delivery_cost_actual flatFeePrice,sob.`name` as branch,f.shop_type
                           from sdb_wms_deliverycomp a 
                           inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id
                           inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id
                           inner join sdb_wms_deliverynote  dn on dn.id=d.deliverynote_id
                           inner join sdb_archive_wmsdelivery e on d.delivery_bn = e.delivery_bn
                           inner join sdb_archive_wmsdelivery_bill db on e.delivery_id = db.delivery_id 
                           inner join sdb_archive_wmsdelivery_items g on e.delivery_id = g.delivery_id 
                           inner join sdb_material_basic_material smbm on smbm.material_bn=g.bn
                           INNER JOIN sdb_material_basic_material_ext smbme on smbm.bm_id=smbme.bm_id
                           inner join sdb_ome_shop f on e.shop_id = f.shop_id
                           inner JOIN sdb_archive_delivery od on ( e.outer_delivery_bn = od.delivery_bn )
                           inner JOIN sdb_archive_delivery_order odo on ( od.delivery_id = odo.delivery_id )
                           inner JOIN sdb_archive_orders os on (odo.order_id = os.order_id)
                           inner JOIN sdb_ome_branch sob on (sob.branch_id = e.branch_id)
                           where ".$where." ";

            $omedelivery = $this->db->selectLimit($sql,$end,$start);

            foreach ($omedelivery as $key => $value) 
            {
                $salesPlatfrom = '其他';
               if ($value['shop_type'] != '') {
                   $salesPlatfrom = ome_shop_type::shop_name($value['shop_type']);
               }
                $omedelivery[$key]['shop_type'] = $salesPlatfrom;
                $divide_order_fee = $orderItem ->getlist('divide_order_fee,nums',array('bn'=>$value['bn'],'order_id'=>$value['order_id']));
                if($is_price[0]['is_price'] == 'true') {
                    $omedelivery[$key]['divide_order_fee'] = round($divide_order_fee[0]['divide_order_fee']/$divide_order_fee[0]['nums'],4);
                }else{
                    $omedelivery[$key]['divide_order_fee'] = '-';
                    $omedelivery[$key]['flatFeePrice'] = '-';
                }
                unset($omedelivery[$key]['order_id']);
            }

            foreach ($omedelivery as $k => $aFilter) {
                $exptmp_data = array();
                foreach ($aFilter as $col => $val) {
                    $aFilter[$col] = mb_convert_encoding($aFilter[$col], 'GBK', 'UTF-8');
                    $exptmp_data[] = $aFilter[$col];
                }
                $data['content']['main'][] = implode(',', $exptmp_data);
            }
            return $data;
        
    }
}
